/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package name.coolwind.cxblog.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import name.coolwind.cxblog.domain.Blog;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

/**
 *
 * @author coolwind
 */
@Repository
public class BlogDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    private final SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

    public void createBlog(Blog blog) {
        String sqlStr = "insert into cx_blog(blog_title, blog_tag, blog_author, blog_content, blog_date) values(?,?,?,?,?)";
        this.jdbcTemplate.update(sqlStr, blog.getBlogTitle(), blog.getBlogTag(),
                blog.getBlogAuthor(), blog.getBlogContent(), df.format(blog.getBlogDate()));
    }

    public void updateBlog(Blog blog) {
        String sqlStr = "update cx_blog set blog_title = ?, blog_tag = ?, blog_content = ? where blog_id = ?";
        this.jdbcTemplate.update(sqlStr, blog.getBlogTitle(), blog.getBlogTag(),
                blog.getBlogContent(), blog.getBlogId(), df.format(blog.getBlogDate()));

    }

    public void deleteBlog(int blogId) {
        String sqlStr = "delete from cx_blog where blog_id = ?";
        this.jdbcTemplate.update(sqlStr, blogId);
    }

    public Blog getBlogByBlogId(final int blogId) {
        String sqlStr = "select * from cx_blog where blog_id = ?";
        final Blog blog = new Blog();
        final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        this.jdbcTemplate.query(sqlStr, new Object[]{blogId}, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                blog.setBlogId(blogId);
                blog.setBlogAuthor(rs.getInt("blog_author"));
                blog.setBlogTag(rs.getInt("blog_tag"));
                blog.setBlogTitle(rs.getString("blog_title"));
                blog.setBlogContent(rs.getString("blog_content"));
                blog.setBlogClick(rs.getInt("blog_click"));
                try {
                    blog.setBlogDate(df.parse(rs.getString("blog_date")));
                    Logger.getLogger(rs.getString("blog_date"));
                } catch (ParseException ex) {
                    Logger.getLogger(BlogDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

        });

        return blog;
    }

    public List<Blog> getSeveralBlogByPage(int pageIndex, int perPage) {
        final List<Blog> blogList = new LinkedList<Blog>();
        String sqlStr = "select * from cx_blog order by blog_date desc limit ?,?";
        this.jdbcTemplate.query(sqlStr, new Object[]{pageIndex, perPage}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Blog blog = new Blog();
                blog.setBlogAuthor(rs.getInt("blog_author"));
                blog.setBlogTitle(rs.getString("blog_title"));
                blog.setBlogTag(rs.getInt("blog_tag"));
                blog.setBlogContent(rs.getString("blog_content"));
                blog.setBlogClick(rs.getInt("blog_click"));
                blog.setBlogId(rs.getInt("blog_id"));
                blog.setBlogDate(rs.getDate("blog_date"));

                blogList.add(blog);
            }
        });

        return blogList;
    }

    public List<String> getTimeIntervalByPage(int pageIndex, int perPage) {
        final List<String> timeInterval = new LinkedList();
        if (perPage == 0) {
            String sqlStr = "select distinct date_format(blog_date,\"%Y年%m月\") from cx_blog order by blog_date desc";
            this.jdbcTemplate.query(sqlStr, new Object[]{pageIndex, perPage}, new RowCallbackHandler() {

                @Override
                public void processRow(ResultSet rs) throws SQLException {
                    timeInterval.add(rs.getString(1));
                }
            });
        } else {
            String sqlStr = "select distinct date_format(blog_date,\"%Y年%m月\") from cx_blog order by blog_date desc limit ?,?";
            this.jdbcTemplate.query(sqlStr, new Object[]{pageIndex, perPage}, new RowCallbackHandler() {

                @Override
                public void processRow(ResultSet rs) throws SQLException {
                    timeInterval.add(rs.getString(1));
                }
            });
        }

        return timeInterval;
    }
    
    public int getAmountOfBlog(){
        String strSql = "select count(*) from cx_blog";
        return this.jdbcTemplate.queryForObject(strSql, Integer.class);
    }
}
